import sqlalchemy
from sqlalchemy import MetaData
from sqlalchemy import select
from sqlalchemy import Table
from sqlalchemy.orm import mapper
from sqlalchemy.orm import synonym

engine = sqlalchemy.create_engine("postgres://migration2:migration2@127.0.0.1:5432/GestAssoc")
metadata = MetaData(bind=engine)

t_students = Table("tb_etudiant", metadata, autoload=True)
t_adhesions = Table("tb_adhesion", metadata, autoload=True)
t_rooms = Table("tb_studio", metadata, autoload=True)
t_switchs = Table("tb_switch", metadata, autoload=True)
t_switchports = Table("tb_switchport", metadata, autoload=True)
t_macs = Table("tb_adressage", metadata, autoload=True)
t_ips = Table("tb_ipa", metadata, autoload=True)
t_ipclasses = Table("tb_ipc", metadata, autoload=True)
t_buildings = Table("tb_batiment", metadata, autoload=True)
t_machines = Table("tb_machine", metadata, autoload=True)


class Machine(object):
  pass

mapper(Machine, t_machines, properties={
  "mac_nom": synonym("name", map_column=True),
  "mac_actif": synonym("active", map_column=True),
})

class Building(object):
  pass

mapper(Building, t_buildings, properties ={
  "bat_lib": synonym("label", map_column=True),
})

class Student(object):
  pass

mapper(Student, t_students, properties={
  'etu_login': synonym('login', map_column=True),
  'etu_forward': synonym('email', map_column=True),
  'etu_mdp': synonym("password", map_column=True),
  'etu_nom': synonym("lastname", map_column=True),
  'etu_prenom': synonym("firstname", map_column=True),
})

class Adhesion(object):
  pass

mapper(Adhesion, t_adhesions, properties={
  'adh_datedeb': synonym('begin', map_column=True),
  'adh_datefin': synonym('end', map_column=True),
  'adh_actif': synonym('active', map_column=True),
})

class Room(object):
  pass

mapper(Room, t_rooms, properties={
  'std_num': synonym('label', map_column=True),
})

class Switch(object):
  pass

mapper(Switch, t_switchs, properties={
  'swt_nom': synonym('name', map_column=True),
  'swt_administrable': synonym("administrable", map_column=True),
})

class Switchport(object):
  pass

mapper(Switchport, t_switchports, properties={
  "swp_num": synonym("number", map_column=True),
})

class Mac(object):
  pass

mapper(Mac, t_macs, properties={
  "adr_mac": synonym("address", map_column=True),
  "adr_actif": synonym("active", map_column=True),
})

class IpClasse(object):
  pass

mapper(IpClasse, t_ipclasses, properties={
  "ipc_lib": synonym("address", map_column=True),
})

class Ip(object):
  def _get_address(self):
    cls = classes_dict[self.ipc_cle]
    return "%s.%s" % (cls.address, self.end)
  address = property(_get_address)

mapper(Ip, t_ips, properties={
  "ipa_lib": synonym("end", map_column=True),
  "ipa_reserve": synonym("booked", map_column=True),
})



from sqlalchemy import orm
sm = orm.sessionmaker(autoflush=True, autocommit=False, bind=engine)
session = orm.scoped_session(sm)

students = session.query(Student).all()
buildings = session.query(Building).all()
rooms = session.query(Room).all()
adhesions = session.query(Adhesion).all()
machines = session.query(Machine).all()
ips = session.query(Ip).all()
classes = session.query(IpClasse).all()
classes_dict = dict([(c.ipc_cle, c) for c in classes])
macs = session.query(Mac).order_by(t_macs.c.adr_actif.asc()).all()
switchs = session.query(Switch).all()
switchports = session.query(Switchport).all()

def getMac(machine):
  """Returns the mac address associated with the machine. If more than one, only
  returns the first one... else return None.
  """
  e = select(["adr_mac"], t_macs.c.mac_cle==machine.mac_cle).distinct()
  if machine.active:
    e = e.where(t_macs.c.adr_actif==True)
  res = session.execute(e).fetchone()
  if res:
    return res[0]
  return None
  
